﻿Imports System.Data.OleDb
Imports System.Data
Imports Excel = Microsoft.Office.Interop.Excel
Public Class QuanLyNhanVien
    Dim k As String
    Dim H As Integer
    Private Sub QuanLyNhanVien_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Me.Tag = "admin" Then
            btnThemNV.Visible = True
            btnCapNhatNV.Visible = True
            btnXoaNV.Visible = True
        Else
            btnCapNhatNV.Visible = False
            btnThemNV.Visible = False
            btnXoaNV.Visible = False
        End If
        Dim bang As DataTable = NhanVien_BUS.loadNhanVien()
        DataGridView1_QLNhanVien.DataSource = bang
    End Sub

    Private Sub btnLayDL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLayDL.Click
        Dim frm As New LayDLExcelNhanVien()
        frm.ShowDialog()
    End Sub

    Private Sub btnXuat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXuat.Click
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "C:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")
        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = "DEMO"
                Dim i As Integer = 1
                For col = 0 To DataGridView1_QLNhanVien.Columns.Count - 1
                    .cells(1, i).value = DataGridView1_QLNhanVien.Columns(col).HeaderText
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To DataGridView1_QLNhanVien.Columns.Count - 1
                    i = 2
                    For row = 0 To DataGridView1_QLNhanVien.Rows.Count - 1
                        With .worksheets(1)
                            .Cells(i, k).Value = DataGridView1_QLNhanVien.Rows(row).Cells(col).Value
                            i += 1
                        End With
                    Next
                    k += 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With

            Excel = Nothing
            MsgBox("Dữ liệu đã được xuất sang định dạng Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXC EL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub

    Private Sub btnCapNhatNV_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCapNhatNV.Click
        Dim nhanvien As New NhanVien_DTO
        If MessageBox.Show("có chắt chắn bạn muốn cập nhật dòng này không", "xác nhận", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1) = DialogResult.Yes Then
            nhanvien.MaLoaiQL = DataGridView1_QLNhanVien.CurrentRow.Cells(0).FormattedValue.ToString()
            nhanvien.HoTenQL = DataGridView1_QLNhanVien.CurrentRow.Cells(1).FormattedValue.ToString()
            nhanvien.DienThoai = DataGridView1_QLNhanVien.CurrentRow.Cells(3).FormattedValue.ToString()
            nhanvien.DiaChi = DataGridView1_QLNhanVien.CurrentRow.Cells(2).FormattedValue.ToString()
            nhanvien.TenQuanLy = DataGridView1_QLNhanVien.CurrentRow.Cells(4).FormattedValue.ToString()
            NhanVien_BUS.UpdateNhanVien(nhanvien)
        End If
    End Sub

    Private Sub btnThemNV_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThemNV.Click
        Dim fr As New ThemNhanVien()
        fr.ShowDialog()
        Dim bang As DataTable = NhanVien_BUS.loadNhanVien()
        DataGridView1_QLNhanVien.DataSource = bang
    End Sub

    Private Sub btnXoaNV_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoaNV.Click
        Dim a As String
        Dim k As Integer

        If MessageBox.Show("có chắt chắn bạn muốn xoá dòng này không", "xác nhận", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1) = DialogResult.Yes Then
            k = DataGridView1_QLNhanVien.RowCount - 2
            a = DataGridView1_QLNhanVien.CurrentRow.Cells(0).FormattedValue.ToString()
            NhanVien_BUS.XoaNhanVien(a)
            DataGridView1_QLNhanVien.Rows.RemoveAt(k)
        End If
    End Sub

    Private Sub btnThoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThoat.Click
        Me.Close()
    End Sub

End Class